﻿Public Class FrmEditions
    Public pRubrique As String
    Public pParametres As String

    Private szSQL As String
    Private rs As ADODB.Recordset

    Private Sub FrmEditions_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim nb As Integer = 0

        ' effacer la listview
        LV_ETATS.Items.Clear()

        ' Lire les états disponibles dans la rubrique
        szSQL = "CALL spGet_P_ETATS_byRUB('" & pRubrique & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)

        Do While Not rs.EOF
            LV_ETATS.Items.Add(rs.Fields("libelle").Value)
            LV_ETATS.Items(nb).SubItems.Add(nv(rs.Fields("nom_rpt").Value))
            LV_ETATS.Items(nb).SubItems.Add(nv(rs.Fields("code").Value))
            LV_ETATS.Items(nb).SubItems.Add(nv(rs.Fields("dates").Value))
            ' Get the next record
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()

    End Sub


    Private Sub LV_ETATS_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_ETATS.DoubleClick

        ' un état est-il sélectionné ?
        If LV_ETATS.SelectedItems.Count <= 0 Then
            Exit Sub
        End If

        Dim noRPT As String = LV_ETATS.SelectedItems(0).SubItems(2).Text
        Dim szNomRPT As String = LV_ETATS.SelectedItems(0).SubItems(1).Text


        Select szNomRPT
            Case "contrat"
                ' imprimer le contrat et ses annexes 
                EditerContrat(pParametres)
                ' ferme la fenetre
                Me.Close()

            Case "courrier"
                ' edition d'un courrier : selectionner un RPT du dossier /COURRIERS
                Dim openDataFileDialog As OpenFileDialog = New OpenFileDialog()
                openDataFileDialog.Filter = "Fichiers Crystal ou Word|*.rpt;*.doc"
                openDataFileDialog.InitialDirectory = Mid(My.Application.Info.DirectoryPath, 1, 10) & "\COURRIERS\"
                If openDataFileDialog.ShowDialog = Windows.Forms.DialogResult.OK Then
                    szSQL = openDataFileDialog.FileName
                Else
                    Exit Sub
                End If
                ' fichier RPT ou DOC
                If LCase(Mid(szSQL, szSQL.Length - 2, 3)) = "rpt" Then
                    FrmViewReport.pCodeEtat = noRPT
                    FrmViewReport.pParamRPT = szSQL   'contient le chemein du RPT séléctionné
                    FrmViewReport.ShowDialog()
                Else
                    EditerWord(szSQL)
                End If

            Case Else ' édition de l'étatRPT
                ' Sélection de DATES ?
                If LV_ETATS.SelectedItems(0).SubItems(3).Text = "OUI" Then
                    frmDates.ShowDialog()
                    ' dates saisies ?
                    If frmDates.pSelection.Length > 0 Then
                        FrmViewReport.pParamRPT = frmDates.pSelection
                    Else
                        Exit Sub
                    End If
                Else
                    FrmViewReport.pParamRPT = ""
                End If

                Select Case noRPT
                    Case 1
                        ' Agenda Moniteur par journée
                        GenereAgendaMoniteur("JOURNEE", gMoniteur1, gDate_debut)

                    Case 29
                        ' Agenda Moniteur par semaine
                        GenereAgendaMoniteur("SEMAINE", gMoniteur1, gDate_debut)

                End Select


                ' édition de l'étatRPT
                FrmViewReport.pCodeEtat = noRPT
                FrmViewReport.ShowDialog()

        End Select
        ' ferme la fenetre
        Me.Close()

    End Sub

    Private Sub LV_ETATS_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_ETATS.KeyDown
        ' touche Enter = DoubleClick
        If e.KeyCode = Keys.Enter Then
            LV_ETATS_DoubleClick(sender, e)
        End If

    End Sub

    Private Sub EditerContrat(ByVal formule As String)

        ' Lire la formule
        szSQL = "CALL spGet_FORMULES('" & formule & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            szSQL = nv(rs.Fields("contrat1").Value)
            rs.Close()
            ' document 1 défini ? oui, l'éditer sous Word
            If szSQL.Length > 0 Then
                EditerWord(Mid(My.Application.Info.DirectoryPath, 1, 3) & szSQL)
            End If
        End If

    End Sub

    Private Sub EditerWord(ByVal fichierWord As String)
        Dim objWordApp As Word.Application = New Word.Application
        Dim objDoc As Word.Document
        Dim szLieuStage As String = ""

        ' Lire le stage PAP de l'élève
        szSQL = "CALL spGet_PLA_STAGE_LIGNES_byCD_CLI('C'," & gCD_CLI & ",0);"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            If rs.Fields("groupe").Value = "A" Then
                szLieuStage = "CHARPENNES"
            Else
                szLieuStage = "VAUGNERAY"
            End If
        End If
        rs.Close()

        ' Lire l'ELEVE
        szSQL = "CALL spGet_ELEVES_byCD_CLI(" & gCD_CLI & ")"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount < 1 Then
            Exit Sub
        End If

        'Show the Word application window if checked.
        objWordApp.Visible = True

        'Open an existing document.
        Try
            objWordApp.Documents.Open(fichierWord)
        Catch ex As Exception
            MsgBox(ex.Message & " : " & vbCrLf & "Vérifiez le nom du contrat dans la formule de l'élève.", vbCritical)
            Exit Sub
        End Try

        objDoc = objWordApp.ActiveDocument

        'Find and replace CIVILITE
        objDoc.Content.Find.Execute(FindText:="«Civ»", ReplaceWith:=rs.Fields("civilite").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Nom»", ReplaceWith:=rs.Fields("nompren").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Code»", ReplaceWith:=rs.Fields("cd_cli").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Adresse»", ReplaceWith:=nv(rs.Fields("adresse").Value), Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«CPostal»", ReplaceWith:=rs.Fields("codepost").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Ville»", ReplaceWith:=rs.Fields("ville").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«DateNaiss»", ReplaceWith:=rs.Fields("date_nais").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«LieuNaiss»", ReplaceWith:=rs.Fields("lieu_nais").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«DebutStage»", ReplaceWith:=IfDate(rs.Fields("stage_debut").Value), Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«FinStage»", ReplaceWith:=IfDate(rs.Fields("stage_fin").Value), Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Categorie»", ReplaceWith:=rs.Fields("permis_demande").Value, Replace:=Word.WdReplace.wdReplaceAll)
        objDoc.Content.Find.Execute(FindText:="«Solde»", ReplaceWith:=rs.Fields("credit").Value - rs.Fields("debit").Value, Replace:=Word.WdReplace.wdReplaceAll)
        If szLieuStage = "CHARPENNES" Then
            objDoc.Content.Find.Execute(FindText:="«LieuStage»", ReplaceWith:="Agence Marietton Charpennes, 26 Cours Emile ZOLA, 69100 VILLEURBANNE", Replace:=Word.WdReplace.wdReplaceAll)
        ElseIf szLieuStage = "VAUGNERAY" Then
            objDoc.Content.Find.Execute(FindText:="«LieuStage»", ReplaceWith:="Agence Marietton Vaugneray, R.D. 30 Les Aiguillons, 69670 VAUGNERAY", Replace:=Word.WdReplace.wdReplaceAll)
        End If

        objWordApp = Nothing

        rs.Close()
        rs = Nothing

    End Sub

    Private Sub GenereAgendaMoniteur(ByVal pTypeVue As String, pMoniteur As String, pDate As Date)

        If pMoniteur.Length = 0 Then
            MsgBox("Sélectionner d'abord un des moniteurs en cliquant sur son nom.")
        End If

        ' RAZ la table T_HEURES pour extraire les eleves du moniteur
        szSQL = "CALL spDel_T_HEURES('" & gUserCode & "')"
        ExecQuery(ctDSN_AEM, szSQL)

        If pTypeVue = "SEMAINE" Then
            ' imprimer la semaine d'un moniteur
            ' a partir de Lundi
            Dim dLundi As Date = getLundiPrecedent(pDate)
            ' initiliser les 13 heures d'ouverture
            For i = 6 To 20
                szSQL = "INSERT INTO T_HEURES (cd_uti,age_uti,date,cd_mon,heure,libelle1,libelle2,libelle3,libelle4,libelle5,libelle6) VALUE ('" & _
                gUserCode & "'," & gUserAgence & ",'" & Format(dLundi, "yyyy/MM/dd") & "','" & pMoniteur & "'," & i & ",'','','','','','')"
                ExecQuery(ctDSN_AEM, szSQL)
            Next

            ' initialiser la ligne entete
            szSQL = "INSERT INTO T_HEURES (cd_uti,age_uti,date,cd_mon,heure,libelle1,libelle2,libelle3,libelle4,libelle5,libelle6) VALUE ('" & _
             gUserCode & "'," & gUserAgence & ",'" & Format(dLundi, "yyyy/MM/dd") & "','" & pMoniteur & "',0,'" & _
             Format(dLundi, "ddd dd.MM") & "','" & Format(dLundi.AddDays(1), "ddd dd.MM") & "','" & Format(dLundi.AddDays(2), "ddd dd.MM") & "','" & _
             Format(dLundi.AddDays(3), "ddd dd.MM") & "','" & Format(dLundi.AddDays(4), "ddd dd.MM") & "','" & Format(dLundi.AddDays(5), "ddd dd.MM") & "')"
            ExecQuery(ctDSN_AEM, szSQL)

            ' Lire la semaine du moniteur
            szSQL = "CALL spGet_ELEVES_CPT_byPLANNING(" & gUserAgence & ",'" & _
                     Format(dLundi, "yyyy/MM/dd") & "','" & Format(dLundi.AddDays(5), "yyyy/MM/dd") & "','" & pMoniteur & "')"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            Do While Not rs.EOF
                Dim noLig As Integer = rs.Fields("noplan").Value
                Dim noCol As Integer = DateDiff(DateInterval.Day, dLundi, rs.Fields("date").Value) + 1
                ' générer autant de ligne que de qte
                For i = 1 To rs.Fields("qte").Value
                    szSQL = "UPDATE T_HEURES SET libelle" & noCol & "='" & nq(rs.Fields("nom").Value) & _
                            "' WHERE cd_uti='" & gUserCode & "' AND age_uti=" & gUserAgence & " AND date='" & Format(dLundi, "yyyy/MM/dd") & _
                            "' AND cd_mon='" & pMoniteur & "' AND heure=" & noLig + i - 1
                    ExecQuery(ctDSN_AEM, szSQL)
                Next

                rs.MoveNext()
            Loop
        Else
            ' initiliser les 13 heures d'ouverture
            For i = 6 To 20
                szSQL = "INSERT INTO T_HEURES (cd_uti,age_uti,date,cd_mon,heure,cd_cli1,libelle1,libelle2) VALUE ('" & _
                gUserCode & "'," & gUserAgence & ",'" & Format(pDate, "yyyy/MM/dd") & "','" & pMoniteur & "'," & i & ",1,'','')"
                ExecQuery(ctDSN_AEM, szSQL)
            Next
            ' imprimer la journée du moniteur
            szSQL = "CALL spGet_ELEVES_CPT_byPLANNING(" & gUserAgence & ",'" & _
                    Format(pDate, "yyyy/MM/dd") & "','" & Format(pDate, "yyyy/MM/dd") & "','" & pMoniteur & "')"
            GetRecordset(ctDSN_AEM, szSQL, rs)
            Do While Not rs.EOF
                ' générer autant de ligne que de qte
                For i = 1 To rs.Fields("qte").Value
                    szSQL = "UPDATE T_HEURES SET cd_cli1=" & rs.Fields("cd_cli").Value & ", libelle1='" & nq(rs.Fields("nom").Value) & "', libelle2='" & nv(rs.Fields("COMMENT").Value) & _
                            "', libelle3='" & nq(nv(rs.Fields("lieu_rdv").Value)) & "', fin_reservation='" & IfDate(rs.Fields("fin_reservation").Value) & _
                            "' WHERE cd_uti='" & gUserCode & "' AND age_uti=" & gUserAgence & " AND date='" & Format(pDate, "yyyy/MM/dd") & _
                            "' AND cd_mon='" & pMoniteur & "' AND heure=" & rs.Fields("noplan").Value + i - 1
                    ExecQuery(ctDSN_AEM, szSQL)
                Next
                rs.MoveNext()
            Loop
        End If
        rs.Close()

    End Sub


End Class